from typing import List, Optional
from uuid import UUID
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, update, delete
from sqlalchemy.orm import selectinload

from models.document.document_tag import DocumentTag
from models.document.personal_document import PersonalDocument
from schemas.document.document_tag import DocumentTagCreate, DocumentTagUpdate


async def get_tag_by_id(db: AsyncSession, tag_id: UUID) -> Optional[DocumentTag]:
    """根据ID获取标签"""
    result = await db.execute(
        select(DocumentTag).where(DocumentTag.id == tag_id)
    )
    return result.scalar_one_or_none()


async def get_tag_by_name(db: AsyncSession, name: str) -> Optional[DocumentTag]:
    """根据名称获取标签"""
    result = await db.execute(
        select(DocumentTag).where(DocumentTag.name == name)
    )
    return result.scalar_one_or_none()


async def get_all_tags(db: AsyncSession) -> List[DocumentTag]:
    """获取所有标签"""
    result = await db.execute(
        select(DocumentTag).order_by(DocumentTag.name)
    )
    return result.scalars().all()


async def get_tags_with_document_count(db: AsyncSession) -> List[tuple]:
    """获取标签及其关联的文档数量"""
    result = await db.execute(
        select(
            DocumentTag,
            func.count(PersonalDocument.id).label('document_count')
        )
        .outerjoin(DocumentTag.documents)
        .group_by(DocumentTag.id)
        .order_by(DocumentTag.name)
    )
    return result.all()


async def create_tag(db: AsyncSession, tag: DocumentTagCreate) -> DocumentTag:
    """创建新标签"""
    db_tag = DocumentTag(**tag.model_dump())
    db.add(db_tag)
    await db.commit()
    await db.refresh(db_tag)
    return db_tag


async def update_tag(db: AsyncSession, tag_id: UUID, tag: DocumentTagUpdate) -> Optional[DocumentTag]:
    """更新标签信息"""
    # 检查标签是否存在
    db_tag = await get_tag_by_id(db, tag_id)
    if not db_tag:
        return None
    
    # 更新字段
    update_data = tag.model_dump(exclude_unset=True)
    if update_data:
        await db.execute(
            update(DocumentTag)
            .where(DocumentTag.id == tag_id)
            .values(**update_data)
        )
        await db.commit()
        await db.refresh(db_tag)
    
    return db_tag


async def delete_tag(db: AsyncSession, tag_id: UUID) -> bool:
    """删除标签"""
    result = await db.execute(
        delete(DocumentTag).where(DocumentTag.id == tag_id)
    )
    await db.commit()
    return result.rowcount > 0


async def get_or_create_tags_by_names(db: AsyncSession, tag_names: List[str]) -> List[DocumentTag]:
    """根据标签名称获取或创建标签"""
    tags = []
    for name in tag_names:
        # 先尝试获取现有标签
        existing_tag = await get_tag_by_name(db, name)
        if existing_tag:
            tags.append(existing_tag)
        else:
            # 创建新标签
            new_tag = DocumentTag(name=name)
            db.add(new_tag)
            # 需要flush来获取新标签的ID，但不提交事务
            await db.flush()
            tags.append(new_tag)
    
    # 不在这里提交事务，让调用者管理事务
    return tags


async def search_tags_by_name(db: AsyncSession, search_term: str) -> List[DocumentTag]:
    """根据名称搜索标签"""
    result = await db.execute(
        select(DocumentTag)
        .where(DocumentTag.name.ilike(f"%{search_term}%"))
        .order_by(DocumentTag.name)
    )
    return result.scalars().all()


async def get_popular_tags(db: AsyncSession, limit: int = 10) -> List[tuple]:
    """获取最受欢迎的标签（按关联文档数量排序）"""
    result = await db.execute(
        select(
            DocumentTag,
            func.count(PersonalDocument.id).label('document_count')
        )
        .join(DocumentTag.documents)
        .group_by(DocumentTag.id)
        .order_by(func.count(PersonalDocument.id).desc())
        .limit(limit)
    )
    return result.all()
